﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using SH.STD.Model;
using SH.STD.DataProvider;
using System.Data;

namespace SH.STD.Web.Achievement
{
    public partial class AchievementList : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            this.Master.SetTab(TabType.Achievement);

            if (!Page.IsPostBack)
            {
                this.BindData(this.GetSql());
                this.BindDepartment();
                this.BindRecommendDepartment();
            }
        }

        private void BindData(string sql)
        {
            var dt = Cmn.DB.getDataTable(sql);

            this.rptAchievements.DataSource = dt;
            this.rptAchievements.DataBind();
            this.lblCnt1.Text = dt.Rows.Count.ToString();
        }

        private void BindDepartment()
        {
            var sql = "select ID,Name from Department where Status=1 order by ID";
            this.ddlDepartment.Items.Clear();
            this.ddlDepartment.Items.Add(new ListItem("-请选择-", "-1"));
            var dt = Cmn.DB.getDataTable(sql);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                this.ddlDepartment.Items.Add(new ListItem(dt.Rows[i][1].ToString(), dt.Rows[i][0].ToString()));
            }
        }

        private void BindRecommendDepartment()
        {
            var sql = "select ID,Name from Department where Status=1 order by ID";
            this.ddlRecommendDepartment.Items.Clear();
            this.ddlRecommendDepartment.Items.Add(new ListItem("-请选择-", "-1"));
            var dt = Cmn.DB.getDataTable(sql);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                this.ddlRecommendDepartment.Items.Add(new ListItem(dt.Rows[i][1].ToString(), dt.Rows[i][0].ToString()));
            }
        }

        protected void btnSearch_Click(object sender, EventArgs e)
        {
            this.BindData(this.GetSql());
        }
        protected void btnClear_Click(object sender, EventArgs e)
        {
            this.txtName.Text = String.Empty;
            this.txtMemberName.Text = String.Empty;
            this.txtCertNo.Text = String.Empty;
            this.txtRegNo.Text = String.Empty;
            this.txtEvaluateStartDate.Text = String.Empty;
            this.txtEvaluateEndDate.Text = String.Empty;
            this.ddlDepartment.SelectedIndex = -1;
            this.ddlRecommendDepartment.SelectedIndex = -1;

            this.BindData(this.GetSql());
        }

        private string GetSql()
        {
            var ret = @"
select a.ID,a.Name,a.RegNO,a.EvaluateDate,a.CertNo,a.DepID,a.RecommendDepID,b.Name as DepName
from Achievement a
join Department b
on a.DepID=b.ID
where a.status=1 and ";
            var where = String.Empty;
            if (this.ddlDepartment.SelectedIndex > 0)
            {
                where += "a.DepID=" + this.ddlDepartment.SelectedValue + " and ";
            }
            if (!String.IsNullOrEmpty(this.txtEvaluateStartDate.Text.Trim()))
            {
                where += "a.EvaluateDate>='" + this.txtEvaluateStartDate.Text.Trim() + "' and ";
            }
            if (!String.IsNullOrEmpty(this.txtEvaluateEndDate.Text.Trim()))
            {
                where += "a.EvaluateDate<='" + this.txtEvaluateEndDate.Text.Trim() + "' and ";
            }
            if (!String.IsNullOrEmpty(this.txtRegNo.Text.Trim()))
            {
                where += "a.RegNo like '%" + this.txtRegNo.Text.Trim() + "%' and ";
            }
            if (!String.IsNullOrEmpty(this.txtCertNo.Text.Trim()))
            {
                where += "a.CertNo like '%" + this.txtCertNo.Text.Trim() + "%' and ";
            }
            if (!String.IsNullOrEmpty(this.txtMemberName.Text.Trim()))
            {
                where +=
@"a.ID in 
(   
    select a.ID 
    from Member a 
    join Employee b
    on a.EmployeeID=b.ID
    where a.TableID=2 and b.Name like '%" + this.txtMemberName.Text.Trim() + @"%'
) and ";
            }
            if (!String.IsNullOrEmpty(this.txtName.Text.Trim()))
            {
                where += "a.Name like '%" + this.txtName.Text.Trim() + "%' and ";
            }
            ret = where == String.Empty ? ret.Substring(0, ret.LastIndexOf("and ")) : ret + " " + where.Substring(0, where.LastIndexOf("and "));

            return ret;
        }

        public static string ExportTable(DataTable dt)
        {
            var columnNames = new Dictionary<string, string>();
            columnNames.Add("成果名称", "Name");
            columnNames.Add("成果登记号", "RegNo");
            columnNames.Add("评价日期", "EvaluateDate");
            columnNames.Add("证书编号", "CertNo");
            columnNames.Add("完成单位", "DepName");
            string ret = "<table cellspacing=\"0\" cellpadding=\"5\" rules=\"all\" border=\"1\">";
            ret += "<tr style=\"font-weight: bold; white-space: nowrap;\">";
            foreach (var col in columnNames)
            {
                ret += "<td>" + col.Key + "</td>";
            }
            ret += "</tr>";
            foreach (DataRow row in dt.Rows)
            {
                ret += "<tr>";
                foreach (var col in columnNames)
                {
                    var colValue = (row[col.Value].ToString() != "undefined" ? row[col.Value].ToString() : "");
                    if (col.Value.Contains("Date") && !String.IsNullOrEmpty(colValue))
                    {
                        colValue = DateTime.Parse(colValue).ToString("yyyy-MM-dd");
                    }
                    ret += "<td>" + colValue + "</td>";
                }
                ret += "</tr>";
            }
            ret += "</table>";

            return ret;
        }

        protected void btnExport_Click(object sender, EventArgs e)
        {
            var dt = Cmn.DB.getDataTable(this.GetSql());
            ExportDsToXls(this.Page, HttpUtility.UrlEncode("成果管理", System.Text.Encoding.UTF8), dt);
        }

        public static void ExportDsToXls(Page page, string fileName, DataTable dt)
        {
            page.Response.Clear();
            page.Response.Buffer = true;
            page.Response.Charset = "GB2312";
            page.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + System.DateTime.Now.ToString("_yyMMdd_hhmm") + ".xls");
            page.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            page.Response.ContentType = "application/ms-excel";
            page.EnableViewState = false;
            page.Response.Write(ExportTable(dt));
            page.Response.End();
        }

    }
}